package com.cennavi.bigdata.baotou.dao.impl;

import com.cennavi.bigdata.baotou.dao.IndexDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * Created by cennavi on 2018/10/15.
 */
@Repository
public class IndexDaoImpl implements IndexDao{

    @Autowired
    private JdbcTemplate jdbcTemplatePG;

    @Override
    public List<Map<String, Object>> listArea() {

        String sql = "SELECT a.teamid,a.teamname from team a ORDER BY  a.teamid";
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> DgListSpeedIndexByDay(String id, String startTime1) {
        String sql = "SELECT a.\"id\",a.\"time\",a.avgindex,a.avgspeed,a.\"hour\" FROM road_area_hour_index A WHERE A .\"id\" = '"+id+"' AND A .\"time\" ="+startTime1.replaceAll("-","")+" ORDER BY a.\"hour\"";
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> searchRoadByName(String roadname) {
        String sql = "SELECT a.\"id\",a.kind,a.roadname||'('||a.startname||'-->'||a.endname||')' as roadname,a.geomtry,st_asgeojson('POINT('||REPLACE(a.latlon,',',' ')||')') as center,a.length from road a WHERE a.roadname LIKE '"+roadname+"%' ORDER BY a.roadname";
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> dataListSpeedIndex(String id, String startTime1, String endTime1) {
        String sql = "SELECT a.roadid as id,a.roadname as name ,a.\"time\",a.avgspeed,a.maxspeed,a.minspeed,a.avgindex,a.maxindex,a.minindex,a.avgtraveltime from road_day_index a WHERE a.roadid='"+id+"' and a.\"time\"="+startTime1+" or a.roadid='"+id+"' AND a.\"time\"="+endTime1+" ORDER BY a.\"time\"";
        System.out.println(sql);
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> dataListSpeedIndexArea(String id, String startTime1, String endTime1) {
        String sql = "SELECT a.areaid as id,a.areaname as name ,a.\"time\",a.avgspeed,a.maxspeed,a.minspeed,a.avgindex,a.maxindex,a.minindex,a.avgtraveltime FROM area_day_index A WHERE (A .areaid = "+id+" AND A .\"time\" = "+startTime1+") OR (A .areaid = "+id+" AND A .\"time\" = "+endTime1+") ORDER BY A .\"time\"";
        System.out.println(sql);
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> DgListSpeedIndexByWeek(String id, String startTime1, String endTime1) {
        String sql = "SELECT a.\"id\", A .\"hour\", round(AVG(A .avgindex) :: NUMERIC, 2) AS avgindex, round(AVG(A .avgspeed) :: NUMERIC, 2) AS avgspeed FROM road_area_hour_index A WHERE A .\"id\" = '"+id+"' AND A .\"time\" >= "+startTime1.replaceAll("-","")+" AND A .\"time\" <= "+endTime1.replaceAll("-","")+" GROUP BY a.\"id\", A .\"hour\"";
        System.out.println(sql);
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> listQuarterOfYear() {
        String sql = "SELECT * from quarterofyear a ORDER BY a.\"id\" DESC";
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> DgListSpeedIndexByQuarter(String id, String startTime1) {
        String sql = "SELECT A .\"hour\", round(AVG(A .avgindex) :: NUMERIC, 2) AS avgindex, round(AVG(A .avgspeed) :: NUMERIC, 2) AS avgspeed FROM road_area_hour_index A WHERE A .\"id\" = '"+id+"' AND A .quarterofyear = "+startTime1+" GROUP BY A .\"hour\" ORDER BY A .\"hour\"";
        System.out.println(sql);
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> dataListSpeedIndexAreaWeek(String id, String startTime, String endTime) {
        String sql = "SELECT A .areaid as id, A .areaname as name, round(AVG(A .avgspeed) :: NUMERIC, 2) AS avgspeed, round(AVG(A .avgindex) :: NUMERIC, 2) AS avgindex, round(AVG(A .maxindex) :: NUMERIC, 2) AS maxindex, round(AVG(A .maxspeed) :: NUMERIC, 2) AS maxspeed, round(AVG(A .minindex) :: NUMERIC, 2) AS minindex, round(AVG(A .minspeed) :: NUMERIC, 2) AS minspeed, round( AVG (A .avgtraveltime) :: NUMERIC, 2 ) AS avgtraveltime FROM area_day_index A WHERE A .areaid = "+id+" AND A .\"time\" >= "+startTime+" AND A .\"time\" <= "+endTime+" GROUP BY A .areaid, A .areaname";
        System.out.println(sql);
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> dataListSpeedIndexRoadWeek(String id, String startTime, String endTime) {
        String sql = "SELECT A .roadid as id, A .roadname as name, round(AVG(A .avgspeed) :: NUMERIC, 2) AS avgspeed, round(AVG(A .avgindex) :: NUMERIC, 2) AS avgindex, round(AVG(A .maxindex) :: NUMERIC, 2) AS maxindex, round(AVG(A .maxspeed) :: NUMERIC, 2) AS maxspeed, round(AVG(A .minindex) :: NUMERIC, 2) AS minindex, round(AVG(A .minspeed) :: NUMERIC, 2) AS minspeed, round( AVG (A .avgtraveltime) :: NUMERIC, 2 ) AS avgtraveltime FROM road_day_index A WHERE A .roadid = '"+id+"' AND A .\"time\" >= "+startTime+" AND A .\"time\" <= "+endTime+" GROUP BY A .roadid, A .roadname";
        System.out.println(sql);
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> dataListSpeedIndexAreaQuarter(String id, String startTime) {
        String sql = "SELECT A .areaid as id, A .areaname as name, round(AVG(A .avgspeed) :: NUMERIC, 2) AS avgspeed, round(AVG(A .avgindex) :: NUMERIC, 2) AS avgindex, round(AVG(A .maxindex) :: NUMERIC, 2) AS maxindex, round(AVG(A .maxspeed) :: NUMERIC, 2) AS maxspeed, round(AVG(A .minindex) :: NUMERIC, 2) AS minindex, round(AVG(A .minspeed) :: NUMERIC, 2) AS minspeed, round( AVG (A .avgtraveltime) :: NUMERIC, 2 ) AS avgtraveltime FROM area_day_index A WHERE A .areaid = "+id+" AND a.quarterofyear = "+startTime+" GROUP BY A .areaid, A .areaname";
        System.out.println(sql);
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> dataListSpeedIndexRoadQuarter(String id, String startTime) {
        String sql = "SELECT a.roadid as id, A .roadname as name, round(AVG(A .avgspeed) :: NUMERIC, 2) AS avgspeed, round(AVG(A .avgindex) :: NUMERIC, 2) AS avgindex, round(AVG(A .maxindex) :: NUMERIC, 2) AS maxindex, round(AVG(A .maxspeed) :: NUMERIC, 2) AS maxspeed, round(AVG(A .minindex) :: NUMERIC, 2) AS minindex, round(AVG(A .minspeed) :: NUMERIC, 2) AS minspeed, round( AVG (A .avgtraveltime) :: NUMERIC, 2 ) AS avgtraveltime FROM road_day_index A WHERE A .roadid = '"+id+"' AND a.quarterofyear = "+startTime+" GROUP BY A .roadid, A .roadname";
        System.out.println(sql);
        return jdbcTemplatePG.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> dataListRoadPredict(String roadid, String dateStr,short iswork) {
        String sql = "SELECT a.roadid,a.speed,a.\"index\",a.traveltime from road_predict_base a WHERE a.roadid = '"+roadid+"' AND a.\"time\"='"+dateStr+"' AND a.iswork="+iswork;
        System.out.println(sql);
        return jdbcTemplatePG.queryForList(sql);
    }
}
